﻿CREATE FUNCTION [dbo].[Collection_CollectBy](
	@LoanID int
)RETURNS sysname AS BEGIN
DECLARE @C TABLE(sAMAccountName sysname)
INSERT INTO @C
SELECT sAMAccountName FROM v_ADS_Login

RETURN LOWER(dbo.UserName())

RETURN 
LOWER(
ISNULL(
(
SELECT  TOP 1
	CASE WHEN N.NoteUser = N.DateDueBy OR dbo.UserName() <> N.NoteUser OR dbo.IsUserCollector() = 0
			 THEN N.DateDueBy ELSE dbo.UserName() END
FROM (
 SELECT MAX(N.NoteDate) AS NoteDate FROM 
(	SELECT NoteDate,NoteUser FROM t_Note N WITH(nolock)
	WHERE (N.LoanID = @LoanID) AND N.NoteTypeID = dbo.enum__NoteType__CollectionActivity() AND
				 N.NoteDate0 < dbo.Today(GETDATE()) 
) N
INNER JOIN @C C ON N.NoteUser = C.sAMAccountName
--AND NOT NoteUser IN ('USIX','SMALLBIG') AND StatusID <> dbo.fs_StateID__Notes__FromUSIX()
) AS NL INNER JOIN t_Note AS N WITH(nolock) ON NL.NoteDate = N.NoteDate AND (N.LoanID = @LoanID)

),dbo.UserName()
)
)
END
